package report

import (
	"../common"
	"../web"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"strconv"
	"strings"
	"time"
)

type QueryWoObj struct {
	common.BaseHandler
}

type WoQueryPageObj struct {
	common.BaseHandler
}

type DownloadWoObj struct {
	common.BaseHandler
}

func (self *QueryWoObj) Prepare() {}

func (self *QueryWoObj) ParameterCheck() bool {
	s_wono := self.Ctx.Params["q"]
	if s_wono != "" {
		self.Sqlparas = append(self.Sqlparas, s_wono)
		self.SqlQueryExecute()
		return false
	} else {
		s_wono = strings.TrimSpace(self.Ctx.Params["wono"])
	}
	if s_wono == "" {
		return false
	}

	self.Sqlparas = append(self.Sqlparas, s_wono)

	return true
}

func (self *QueryWoObj) SqlQueryExecute() {
	s_wono := self.Sqlparas[0].(string)

	ssn_range := *common.FetchOneRowPtr(
		`SELECT ISNULL(MIN(SSN_NO),''), ISNULL(MAX(SSN_NO),'') 
           FROM SFC_SSN(NOLOCK) 
          WHERE WONO=?`,
		self.Usr.Conn, new(common.WoRange), s_wono)

	prod_range := *common.FetchOneRowPtr(
		`SELECT ISNULL(CONVERT(NVARCHAR(23),MIN(MODIFYDATE),121),''),ISNULL(CONVERT(NVARCHAR(23),MAX(MODIFYDATE),121),'')
		   FROM SFC_SSNFLOW(NOLOCK) 
		  WHERE WONO=?`,
		self.Usr.Conn, new(common.WoRange), s_wono)

	routlist := *common.FetchAllRowsPtr(
		`SELECT A.CURR_STATION
		   FROM SFC_ROUTING A(NOLOCK), SFC_WO B(NOLOCK) 
		  WHERE A.NAME=B.ROUTING 
		    AND B.WONO=? 
		  ORDER BY IDX`,
		self.Usr.Conn, new(common.RecDataStr), s_wono)

	total_map := make(map[string]*common.AnalyseDetails)
	totals := &common.AnalyseDetails{"Total", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "100.0", "100.0", "100.0", "100.0", "100.0", "100.0"}
	for _, val := range routlist {
		curr_stat := strings.TrimSpace(val.(common.RecDataStr).Data)
		_, ok := total_map[curr_stat]
		if !ok {
			total_map[curr_stat] = &common.AnalyseDetails{curr_stat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "100.0", "100.0", "100.0", "100.0", "100.0", "100.0"}
		}
	}

	proc_num := *common.FetchAllRowsPtr(
		`SELECT DISTINCT SSN_NO, CURR_STATION
	   	   FROM SFC_SSNFLOW(NOLOCK)
	      WHERE WONO=?`,
		self.Usr.Conn, new(common.NormalStruct), s_wono)
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.NormalStruct).Value2)
		_, ok := total_map[stat]
		if ok {
			total_map[stat].Input = total_map[stat].Input + 1
			totals.Input = totals.Input + 1
		}
	}

	proc_num = *common.FetchAllRowsPtr(
		`SELECT DISTINCT SSN_NO, CURR_STATION
	   	   FROM SFC_SSNFLOW(NOLOCK)
	      WHERE WONO=?
	        AND RESULT='PASS'`,
		self.Usr.Conn, new(common.NormalStruct), s_wono)
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.NormalStruct).Value2)
		_, ok := total_map[stat]
		if ok {
			total_map[stat].Output = total_map[stat].Output + 1
			totals.Output = totals.Output + 1
		}
	}

	proc_num = *common.FetchAllRowsPtr(
		`SELECT CURR_STATION, RESULT, COUNT(1) AS CNT
		   FROM SFC_SSN(NOLOCK)
		  WHERE CURR_STATION IN 
		  		(
		  			SELECT STATION 
		  			  FROM SFC_STATION(NOLOCK)
		  		)
			AND WONO=?
		  GROUP BY CURR_STATION, RESULT`,
		self.Usr.Conn, new(common.ParasStruct3), s_wono)
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value1)
		rsts := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		icnt, _ := strconv.Atoi(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if rsts == "PASS" {
				total_map[stat].Inprocess = icnt
				totals.Inprocess = totals.Inprocess + icnt
			} else {
				total_map[stat].FailUnit = icnt
				totals.FailUnit = totals.FailUnit + icnt
			}
		}
	}

	proc_num = *common.FetchAllRowsPtr(
		`SELECT RN=ROW_NUMBER() OVER(PARTITION BY SSN_NO, CURR_STATION ORDER BY MODIFYDATE), CURR_STATION, RESULT
		   FROM SFC_SSNFLOW(NOLOCK) 
		  WHERE EXISTS 
		  		(
					SELECT 1 
					  FROM 
					  (
						SELECT DISTINCT SSN_NO, CURR_STATION
				   	      FROM SFC_SSNFLOW(NOLOCK)
				         WHERE WONO=?
					  ) A 
				  	 WHERE A.SSN_NO=SFC_SSNFLOW.SSN_NO AND A.CURR_STATION=SFC_SSNFLOW.CURR_STATION
				)`,
		self.Usr.Conn, new(common.ParasStruct3), s_wono)
	for _, val := range proc_num {
		idxd, _ := strconv.Atoi(val.(common.ParasStruct3).Value1)
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		rsts := strings.TrimSpace(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if idxd == 1 {
				if rsts == "PASS" {
					total_map[stat].PUnit = total_map[stat].PUnit + 1
					totals.PUnit = totals.PUnit + 1
				} else {
					total_map[stat].F1Unit = total_map[stat].F1Unit + 1
					totals.F1Unit = totals.F1Unit + 1
				}
			}
			if idxd == 2 {
				if rsts != "PASS" {
					total_map[stat].F2Unit = total_map[stat].F2Unit + 1
					totals.F2Unit = totals.F2Unit + 1
				}
			}
			if idxd > 2 {
				if rsts != "PASS" {
					total_map[stat].F3Unit = total_map[stat].F3Unit + 1
					totals.F3Unit = totals.F3Unit + 1
				}
			}
		}
	}

	proc_num = *common.FetchAllRowsPtr(
		`SELECT SSN_NO, FAILSTATION, COUNT(1) AS CNT
		   FROM SFC_REPAIR(NOLOCK) 
		  WHERE EXISTS 
		  		(
					SELECT 1 
					  FROM 
					  (
						SELECT DISTINCT SSN_NO, CURR_STATION
				   	      FROM SFC_SSNFLOW(NOLOCK)
				         WHERE WONO=?
					  ) A 
				  	 WHERE A.SSN_NO=SFC_REPAIR.SSN_NO AND A.CURR_STATION=SFC_REPAIR.FAILSTATION
				)
		  GROUP BY SSN_NO, FAILSTATION`,
		self.Usr.Conn, new(common.ParasStruct3), s_wono)
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		icnt, _ := strconv.Atoi(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if icnt == 1 {
				total_map[stat].P1Unit = total_map[stat].P1Unit + 1
				totals.P1Unit = totals.P1Unit + 1
			}
			if icnt == 2 {
				total_map[stat].P2Unit = total_map[stat].P2Unit + 1
				totals.P2Unit = totals.P2Unit + 1
			}
			if icnt > 2 {
				total_map[stat].P3Unit = total_map[stat].P3Unit + 1
				totals.P3Unit = totals.P3Unit + 1
			}
		}
	}

	totalfpyv := 1.0
	for _, v := range total_map {
		if v.Input == 0 {
			v.FpyYield = "100.0"
			v.PyYield = "100.0"
			v.RpYield1 = "100.0"
			v.RpYield2 = "100.0"
			v.RpYield3 = "100.0"
			continue
		}
		subfpyv := float64(v.PUnit) / float64(v.Input) * 100.0
		subpyv := float64(v.Output) / float64(v.Input) * 100.0
		subrpv1 := float64(v.P1Unit) / float64(v.F1Unit) * 100.0
		subrpv2 := float64(v.P2Unit) / float64(v.F2Unit) * 100.0
		subrpv3 := float64(v.P3Unit) / float64(v.F3Unit) * 100.0
		subrpv := float64(v.P1Unit+v.P2Unit+v.P3Unit) / float64(v.F1Unit+v.F2Unit+v.F3Unit) * 100.0
		if v.F1Unit == 0 {
			subrpv1 = 100.0
		}
		if subrpv1 > 100.0 {
			subrpv1 = 100.0
		}
		if v.F2Unit == 0 {
			subrpv2 = 100.0
		}
		if subrpv2 > 100.0 {
			subrpv2 = 100.0
		}
		if v.F3Unit == 0 {
			subrpv3 = 100.0
		}
		if subrpv3 > 100.0 {
			subrpv3 = 100.0
		}
		if v.F1Unit+v.F2Unit+v.F3Unit == 0 {
			subrpv = 100.0
		}
		if subrpv > 100.0 {
			subrpv = 100.0
		}
		if subfpyv > 0.0 && subfpyv < 100.0 {
			v.FpyYield = strconv.FormatFloat(subfpyv, 'f', 2, 64)
			totalfpyv = totalfpyv * subfpyv / 100.0
		} else {
			v.FpyYield = strconv.FormatFloat(subfpyv, 'f', 1, 64)
		}
		if subpyv > 0.0 && subpyv < 100.0 {
			v.PyYield = strconv.FormatFloat(subpyv, 'f', 2, 64)
		} else {
			v.PyYield = strconv.FormatFloat(subpyv, 'f', 1, 64)
		}
		if subrpv1 > 0.0 && subrpv1 < 100.0 {
			v.RpYield1 = strconv.FormatFloat(subrpv1, 'f', 2, 64)
		} else {
			v.RpYield1 = strconv.FormatFloat(subrpv1, 'f', 1, 64)
		}
		if subrpv2 > 0.0 && subrpv2 < 100.0 {
			v.RpYield2 = strconv.FormatFloat(subrpv2, 'f', 2, 64)
		} else {
			v.RpYield2 = strconv.FormatFloat(subrpv2, 'f', 1, 64)
		}
		if subrpv3 > 0.0 && subrpv3 < 100.0 {
			v.RpYield3 = strconv.FormatFloat(subrpv3, 'f', 2, 64)
		} else {
			v.RpYield3 = strconv.FormatFloat(subrpv3, 'f', 1, 64)
		}
		if subrpv > 0.0 && subrpv < 100.0 {
			v.RpYield = strconv.FormatFloat(subrpv, 'f', 2, 64)
		} else {
			v.RpYield = strconv.FormatFloat(subrpv, 'f', 1, 64)
		}
		v.F1Unit = v.F1Unit - v.P1Unit
		v.F2Unit = v.F2Unit - v.P2Unit
		v.F3Unit = v.F3Unit - v.P3Unit
		if v.F1Unit < 0 {
			v.F1Unit = 0
		}
		if v.F2Unit < 0 {
			v.F2Unit = 0
		}
		if v.F3Unit < 0 {
			v.F3Unit = 0
		}
	}
	if totals.Input == 0 {
		totals.FpyYield = "100.0"
		totals.PyYield = "100.0"
		totals.RpYield1 = "100.0"
		totals.RpYield2 = "100.0"
		totals.RpYield3 = "100.0"
	} else {
		if totalfpyv > 0.0 && totalfpyv < 1.0 {
			totals.FpyYield = strconv.FormatFloat(totalfpyv*100.0, 'f', 2, 64)
		} else {
			totals.FpyYield = strconv.FormatFloat(totalfpyv*100.0, 'f', 1, 64)
		}
		totalpyv := float64(totals.Output) / float64(totals.Input) * 100.0
		if totalpyv > 0.0 && totalpyv < 100.0 {
			totals.PyYield = strconv.FormatFloat(totalpyv, 'f', 2, 64)
		} else {
			totals.PyYield = strconv.FormatFloat(totalpyv, 'f', 1, 64)
		}
		totalrpv1 := float64(totals.P1Unit) / float64(totals.F1Unit) * 100.0
		totalrpv2 := float64(totals.P2Unit) / float64(totals.F2Unit) * 100.0
		totalrpv3 := float64(totals.P3Unit) / float64(totals.F3Unit) * 100.0
		totalrpv := float64(totals.P1Unit+totals.P2Unit+totals.P3Unit) / float64(totals.F1Unit+totals.F2Unit+totals.F3Unit) * 100.0
		if totals.F1Unit == 0 {
			totalrpv1 = 100.0
		}
		if totalrpv1 > 100.0 {
			totalrpv1 = 100.0
		}
		if totals.F2Unit == 0 {
			totalrpv2 = 100.0
		}
		if totalrpv2 > 100.0 {
			totalrpv2 = 100.0
		}
		if totals.F3Unit == 0 {
			totalrpv3 = 100.0
		}
		if totalrpv3 > 100.0 {
			totalrpv3 = 100.0
		}
		if totals.F1Unit+totals.F2Unit+totals.F3Unit == 0 {
			totalrpv = 100.0
		}
		if totalrpv > 100.0 {
			totalrpv = 100.0
		}
		if totalrpv1 > 0.0 && totalrpv1 < 100.0 {
			totals.RpYield1 = strconv.FormatFloat(totalrpv1, 'f', 2, 64)
		} else {
			totals.RpYield1 = strconv.FormatFloat(totalrpv1, 'f', 1, 64)
		}
		if totalrpv2 > 0.0 && totalrpv2 < 100.0 {
			totals.RpYield2 = strconv.FormatFloat(totalrpv2, 'f', 2, 64)
		} else {
			totals.RpYield2 = strconv.FormatFloat(totalrpv2, 'f', 1, 64)
		}
		if totalrpv3 > 0.0 && totalrpv3 < 100.0 {
			totals.RpYield3 = strconv.FormatFloat(totalrpv3, 'f', 2, 64)
		} else {
			totals.RpYield3 = strconv.FormatFloat(totalrpv3, 'f', 1, 64)
		}
		if totalrpv > 0.0 && totalrpv < 100.0 {
			totals.RpYield = strconv.FormatFloat(totalrpv, 'f', 2, 64)
		} else {
			totals.RpYield = strconv.FormatFloat(totalrpv, 'f', 1, 64)
		}
		totals.F1Unit = totals.F1Unit - totals.P1Unit
		totals.F2Unit = totals.F2Unit - totals.P2Unit
		totals.F3Unit = totals.F3Unit - totals.P3Unit
		if totals.F1Unit < 0 {
			totals.F1Unit = 0
		}
		if totals.F2Unit < 0 {
			totals.F2Unit = 0
		}
		if totals.F3Unit < 0 {
			totals.F3Unit = 0
		}
	}

	var total_sta []*common.AnalyseDetails
	for _, val := range routlist {
		curr_stat := strings.TrimSpace(val.(common.RecDataStr).Data)
		total_sta = append(total_sta, total_map[curr_stat])
	}

	wo_main := *common.FetchOneRowPtr(
		`SELECT A.WONO,A.WOTYPE,A.REV,A.ITEM,B.DESCRIP,A.QTY,ISNULL(A.ROUTING,''),ISNULL(A.REMARK, '')
           FROM SFC_WO A(NOLOCK)
           JOIN SFC_ITEMS B(NOLOCK)
             ON A.ITEM=B.ITEM
          WHERE A.WONO=?`,
		self.Usr.Conn, new(common.WoMain), s_wono)

	wo_ls := *common.FetchAllRowsPtr(
		`SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY SSN_NO), SSN_NO, 
                 CURR_STATION, RESULT, ISNULL(FAILCODE, '') 
           FROM SFC_SSN(NOLOCK) 
          WHERE WONO=?`,
		self.Usr.Conn, new(common.WoDetail), s_wono)

	curr_id := 1
	iRtnData := *common.FetchOneRowPtr(
		`SELECT COUNT(SSN_NO)
           FROM SFC_SSN(NOLOCK) 
          WHERE WONO=?`,
		self.Usr.Conn, new(common.RecCount), s_wono)
	recnum := iRtnData.(common.RecCount).Number
	total_id := recnum / 10
	if recnum%10 > 0 {
		total_id = total_id + 1
	}

	self.TmplData["ssn_range"] = ssn_range
	self.TmplData["prod_range"] = prod_range
	self.TmplData["total_sta"] = total_sta
	self.TmplData["totals"] = totals
	self.TmplData["wo_main"] = wo_main
	self.TmplData["wo_ls"] = wo_ls
	self.TmplData["curr_page"] = curr_id
	self.TmplData["total_page"] = total_id
	self.TmplData["downloads"] = strings.ToUpper(s_wono)
}

func QueryWonoHandler(ctx *web.Context) {
	self := new(QueryWoObj)
	self.Ctx = ctx
	self.Html = "querywo.html"
	self.Rightlist = []string{"querywo"}
	self.DoQuery(self)
}

func (self *WoQueryPageObj) ParameterCheck() bool {
	s_wono := self.Ctx.Params["wono"]
	s_page := self.Ctx.Params["pageid"]
	if s_wono == "" {
		return false
	}
	pageid, err := strconv.Atoi(s_page)
	if err != nil {
		return false
	}

	idx := 10 * (pageid - 1)
	self.Sqlparas = append(self.Sqlparas, s_wono)
	self.Sqlparas = append(self.Sqlparas, idx)

	return true
}

func (self *WoQueryPageObj) SqlQueryExecute() {
	wonos := *common.FetchAllRowsPtr(
		`SELECT TOP 10 A.AUTOID, A.SSN_NO, A.CURR_STATION, A.RESULT, A.FCOD
           FROM
                (
                    SELECT ROW_NUMBER() OVER(ORDER BY SSN_NO) AS AUTOID, SSN_NO, 
                            CURR_STATION, RESULT, ISNULL(FAILCODE, '') AS FCOD
                      FROM SFC_SSN(NOLOCK) 
                     WHERE WONO=?
                ) A
          WHERE A.AUTOID > ?`,
		self.Usr.Conn, new(common.WoDetail), self.Sqlparas...)

	dict_js, _ := json.Marshal(map[string]interface{}{"wonos": wonos, "lang": self.Lang})
	self.Ctx.ResponseWriter.Write(dict_js)
}

func SplitWoByPageHandler(ctx *web.Context) {
	self := new(WoQueryPageObj)
	self.Ctx = ctx
	self.DoJsonQuery(self)
}

func (self *DownloadWoObj) ParameterCheck() bool {
	s_wono := self.Ctx.Params["wonos"]

	self.Sqlparas = append(self.Sqlparas, s_wono)

	return true
}

func (self *DownloadWoObj) SqlQueryExecute() {
	s_wono := self.Sqlparas[0].(string)
	wo_total := *common.FetchAllRowsPtr(
		`SELECT ROW_NUMBER() OVER(ORDER BY SSN_NO), SSN_NO, CURR_STATION, RESULT,
				 ISNULL(FAILCODE, ''), CONVERT(CHAR(23), MODIFYDATE, 121)
           FROM SFC_SSN(NOLOCK)
          WHERE WONO=?`,
		self.Usr.Conn, new(common.WoTotals), s_wono)

	// create download file
	t := time.Now()
	filename := fmt.Sprintf("%s_%s.csv", s_wono, strconv.FormatInt(t.Unix(), 10))
	f, err := os.Create(filepath.Join("download", "files", filename))
	if err != nil {
		common.Application.SendMail(fmt.Sprintf("Create download file: %s failed. Error: %v\n", filename, err))
		return
	}
	defer f.Close()

	f.WriteString("\xEF\xBB\xBF") // write UTF-8 BOM

	w := csv.NewWriter(f)
	w.Write([]string{"Index", "Serial", "Station", "Result", "Failcode", "Date"})
	for _, val := range wo_total {
		index := strings.TrimSpace(val.(common.WoTotals).Index)
		serial := strings.TrimSpace(val.(common.WoTotals).Serial)
		curr := strings.TrimSpace(val.(common.WoTotals).Curr)
		result := strings.TrimSpace(val.(common.WoTotals).Result)
		fail := strings.TrimSpace(val.(common.WoTotals).Failcode)
		date := strings.TrimSpace(val.(common.WoTotals).Date)
		w.Write([]string{"\t" + index, "\t" + serial, curr, result, fail, "\t" + date})
	}
	w.Flush()

	dict_js, _ := json.Marshal(map[string]interface{}{"filename": filename})
	self.Ctx.ResponseWriter.Write(dict_js)
}

func DownloadWoHandler(ctx *web.Context) {
	self := new(DownloadWoObj)
	self.Ctx = ctx
	self.DoJsonQuery(self)
}
